Database backup easily using DB SQL command

Create a folder  a directory. Before executing SQL command the  cursor we need to specify the path in cursor. Below query will return a listing of all backup files in all databases on a SQL instance.

DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name
-- specify database backup directory
SET @path = 'C:\Backup\'  
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR READ_ONLY FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   
WHILE @@FETCH_STATUS = 0   
BEGIN   
   SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
   BACKUP DATABASE @name TO DISK = @fileName  
   FETCH NEXT FROM db_cursor INTO @name   
END   

Just Copy the above code and paste it in th Query Explorer and specify the path where you want to backup the databases and then Execute. It will automatically backups the databases into the specified Location.

This cursor did it very smart. for me it saved lot of time.

The video explains in detail about how to take backup all databases using SQL Command.